﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NetDiskPRJ.DBUtility;
using System.Data.SqlClient;
using System.Data;

namespace NetDiskPRJ.DAL
{
    public class SysSetting
    {
        public SysSetting()
        {
 
        }
        /// <summary>
        /// 设置数据库连接字符串
        /// <creator>wangl</creator>
        /// <date>2013-02-17</date>
        /// </summary>
        /// <param name="sqlString"></param>
        public void SetSqlConnectionString(string sqlString)
        {
            DbHelperSQL.connectionString = sqlString;
            DBUtility.PubConstant.ConnectionString = sqlString;
        }

        /// <summary>
        /// 获取数据库服务器系统时间，返回的时间精度到毫秒
        /// 不为null ,如果数据库无法获取则取当前本地时间(2012-05-10 wangl 修改)
        /// <creator>wangl</creator>
        /// <date>2013-02-17</date> 
        /// </summary>
        /// <returns>null为获取失败</returns>
        public DateTime GetServerDatetime()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT GETDATE()");
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj != null)
            {
                // modify by wangzhubao 2012年3月4日
                //return DateTime.Parse(obj.ToString());   // 返回精度到秒
                return (DateTime)obj;           // 返回精度到毫秒
            }
            else
            {
                return DateTime.Now;
            }

        }
        /// <summary>
        /// 测试数据库连接是否成功
        /// <creator>wangl</creator>
        /// <date>2013-02-17</date>
        /// </summary>
        /// <returns>
        /// 1 成功 
        /// -1 失败
        /// </returns>
        public int DBConnectTest()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT 1");
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj != null)
            {
                return 1;
            }
            else
            {
                return -1;
            }

        }
        /// <summary>
        /// 关闭数据库池对象
        /// <creator>wangl</creator>
        /// <date>2012-02-17</date>
        /// </summary>
        /// <param name="sqlString"></param>
        public void CloseSqlConnection()
        {
            SqlConnection.ClearAllPools();

        }
        /// <summary>
        /// 获取在线用户数
        /// <creator>wangl</creator>
        /// <date>2013-02-17</date>
        /// </summary>
        /// <param name="appName">应用程序名</param>
        /// <returns></returns>
        public int GetOnlineCount(string appName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT  COUNT(DISTINCT net_address + hostname)");
            strSql.Append(" FROM master.dbo.sysprocesses");
            strSql.Append(" where program_name=@program_name ");
            SqlParameter[] parameters = {
                    new SqlParameter("@program_name", SqlDbType.VarChar)};
            parameters[0].Value = appName;
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj != null)
            {
                return int.Parse(obj.ToString());
            }
            else
            {
                return -1;
            }


        }

        /// <summary>
        /// 获取在线用户列表    
        /// <creator>zhongyl</creator>
        /// <date>2011-12-31</date>
        /// </summary>
        /// <param name="appName">应用程序名</param>
        /// <returns></returns>
        public DataSet GetOnlineList(string appName, string logidname)
        {
            /*
             * 参考SQL 语句 王珠宝
               SELECT  a.spid , 
                  kpid ,
                  blocked , 
                  waittime ,           lastwaittype ,           waitresource , 
                  dbid ,           uid ,           cpu ,           physical_io , 
                  memusage ,      b.last_login_time   as  login_time ,           last_batch , 
                  ecid ,           open_tran ,           status ,           hostname , 
                  program_name ,           hostprocess ,           cmd ,           nt_domain , 
                  nt_username ,           net_address ,           net_library , 
                  fullname , logid , ver_no , login_ip_addr ,
                  c.corp_id , 
                  c.dept_id , 
                  c.phone ,
                  c.fax ,
                  c.mobile ,
                  c.short_no ,
                  c.tel_short_no
                FROM master.dbo.sysprocesses a , 
                     apsuser b left outer join v_employee c
                     on b.emp_id = c.emp_id
                     where loginame = 'houseinfo' and
			               b.logid = a.hostname and 
                           program_name = '易灵新家'
             */
            StringBuilder strSql = new StringBuilder();
            //strSql.Append("select y.dept_name,d.brief_name,z.* from (");
            //strSql.Append("  SELECT  a.spid , kpid ,blocked , waittime ,lastwaittype ,waitresource , dbid ,uid , cpu ,physical_io , memusage ,b.last_login_time as login_time  ,last_batch , ecid ,open_tran ,status ,hostname , program_name ,hostprocess ,cmd ,nt_domain , nt_username ,net_address ,net_library , fullname , logid , ver_no , login_ip_addr ,c.corp_id , c.dept_id , c.phone ,c.fax ,c.mobile ,c.short_no ,c.tel_short_no ");
            //strSql.Append(" FROM master.dbo.sysprocesses a , apsuser b left outer join v_employee c on b.emp_id = c.emp_id ");
            //strSql.Append(" where b.logid = a.hostname and program_name=@program_name ");
            //strSql.Append(") as  z,dept y,corp d where y.dept_id=z.dept_id and z.corp_id=d.corp_id ");

            strSql.Append(@"SELECT a.spid , kpid , blocked , 
                              waittime ,          rtrim(lastwaittype) as lastwaittype ,           waitresource , 
                              dbid ,           uid ,           cpu ,           physical_io , 
                              memusage ,      b.FLastLoginTime   as  login_time ,           last_batch , 
                              ecid ,           open_tran ,           status ,           hostname , 
                              program_name ,           hostprocess ,           cmd ,           rtrim(nt_domain) as nt_domain , 
                              rtrim(nt_username) as nt_username ,           net_address ,           net_library , 
                              FUserName , FLogID  , FLastLoginIPAddr ,b.FLastLoginIPAddr,b.FLastLoginTime,b.FAppVersionNo
                               FROM master.dbo.sysprocesses a,dbo.t_PrvUser b 
                            WHERE loginame=@loginame AND b.FLogID=a.hostname  AND program_name=@program_name");

            SqlParameter[] parameters = {
                    new SqlParameter("@program_name", SqlDbType.VarChar),
                    new SqlParameter("@loginame", SqlDbType.VarChar)};
            parameters[0].Value = appName;
            parameters[1].Value = logidname;
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            return ds;
        }

        /// <summary>
        /// 获取刷新后的数据(指定数据)
        /// <creator>wangl</creator>
        /// <date>2013-02-25</date>
        /// </summary>
        /// <param name="colFiledList">列的字段名集合</param>
        /// <param name="tableName">表名</param>
        /// <param name="strWhere">WHERE查询条件</param>
        /// <returns></returns>
        public DataTable GetGridRowNewData(List<string> colFiledList, string tableName, string strWhere)
        {
            string sqlStr = "";
            DataTable dt = null;
            if (colFiledList.Count > 0)
            {
                sqlStr += "SELECT ";
                foreach (string colFiled in colFiledList)
                {
                    sqlStr += colFiled + ",";
                }
                sqlStr = sqlStr.Substring(0, sqlStr.LastIndexOf(','));
                sqlStr += " FROM " + tableName;
                sqlStr += " WHERE " + strWhere;
                try
                {
                    DataSet ds = DbHelperSQL.Query(sqlStr);
                    dt = ds.Tables[0];
                }
                catch
                {

                }
            }
            return dt;
        }
    }
}
